Imports System.Windows.Forms

Public Class frmCalcTSI

    Private Enum enumCalcType
        Chl
        Secchi
        TP
        TN
        Chl_Secchi_TP
        Chl_Secchi_TN
        Chl_Secchi_TP_TN
        Florida
        Invalid
    End Enum

    Private CalcType As enumCalcType

    Private dgvRecords As WRDB.Controls.DGVEditor

    Private Sub frmCalcTSI_FormClosed(ByVal sender As Object, ByVal e As System.Windows.Forms.FormClosedEventArgs) Handles Me.FormClosed
        SaveControlValues(REGAPP, Me)
    End Sub

    Private Sub frmCalcLoad_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        Project.FillPickList(cboChl, clsProject.TempData, "PCodes", "PCode", "Anal_Name", "(Not Used)", , String.Format("WHERE Selected_By='{0}'", clsApplicationSettings.UserName))
        Project.FillPickList(cboSecchi, clsProject.TempData, "PCodes", "PCode", "Anal_Name", "(Not Used)", , String.Format("WHERE Selected_By='{0}'", clsApplicationSettings.UserName))
        Project.FillPickList(cboTP, clsProject.TempData, "PCodes", "PCode", "Anal_Name", "(Not Used)", , String.Format("WHERE Selected_By='{0}'", clsApplicationSettings.UserName))
        Project.FillPickList(cboTN, clsProject.TempData, "PCodes", "PCode", "Anal_Name", "(Not Used)", , String.Format("WHERE Selected_By='{0}'", clsApplicationSettings.UserName))
        Project.FillPickList(cboTSI, "", "PCodes", "PCode", "Anal_Name", "(Create New)")
        Project.FillPickList(cboCCode, "", "CCodes", "CCode", "CCode_Name", "(Create New)")
        frmCalc.RefreshTableList(cboTableName)
        GetControlValues(REGAPP, Me)
        dgvRecords = New Controls.DGVEditor(dgRecords)
    End Sub

    Private Sub btnNext_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnNext.Click
        Try
            With TabControl1
                If .SelectedIndex = 1 Then
                    Dim TableName As String = cboTableName.Text
                    If cboTableName.SelectedIndex = 0 Then TableName = ActiveBrowser.FullTableName
                    Dim DataTable As New clsDataTable(Project, TableName)
                    Try
                        Dim PCode As String = Project.GetPickList(cboTSI)
                        Dim CCode As String = Project.GetPickList(cboCCode)
                        If PCode.StartsWith("(Create") Or CCode.StartsWith("(Create") Then
                            WarningMsg("You must select a valid PCode and CCode before continuing.")
                            Exit Sub
                        End If

                        Dim NumFound As Integer = DataTable.GetCount("PCode='{0}' AND CCode='{1}'", PCode, CCode)
                        If NumFound > 0 Then
                            Select Case MessageBox.Show(StringFormat("Before computing all values, do you want to delete all {0} existing records in this Working table that have the specified PCode ({1}) and CCode ({2})?", NumFound, PCode.Trim, CCode.Trim), "Calculate Trophic State Index", MessageBoxButtons.YesNoCancel, MessageBoxIcon.Question)
                                Case Windows.Forms.DialogResult.Yes
                                    DataTable.DeleteRecords("PCode='{0}' AND CCode='{1}'", PCode, CCode)
                                Case Windows.Forms.DialogResult.No
                                Case Windows.Forms.DialogResult.Cancel
                                    Exit Sub
                            End Select
                        End If

                        DataTable.BeginTransaction()

                        'save computed values to table
                        With dgRecords
                            Dim NumCols As Integer = 1
                            Dim NumComputed As Integer = NumCols * .Rows.Count
                            If MessageBox.Show(StringFormat("You are about to append {0} computed values to the selected Working table ({1}). These were calculated based upon the input PCodes you selected. The calculated values will use the PCode {2} and CCode {3}.\n\nBecause the table will be permanently modified, it may be difficult to undo this operation. Are you sure you want to continue?", NumComputed, TableName, cboTSI.Text, cboCCode.Text), "Calculate Trophic State Index", MessageBoxButtons.OKCancel, MessageBoxIcon.Question) = Windows.Forms.DialogResult.OK Then
                                Try
                                    For i As Integer = 0 To .Rows.Count - 1
                                        If Not PCode.StartsWith("(") Then
                                            Dim DataRecord As New clsDataRecord(.Rows(i).Cells(0).Value, .Rows(i).Cells(1).Value, PCode, .Rows(i).Cells(.ColumnCount - 1).Value, Project.GetPickList(cboCCode))
                                            If Not DataTable.InsertRecord(DataRecord) Then WarningMsg("Unable to update table.") : Exit Sub
                                        End If
                                        MainForm.WriteProgress(i, .Rows.Count - 1, "Saving computed values...", ProgressBarStyle.Continuous)
                                    Next
                                    DataTable.Close(clsDataTable.enumEndTransaction.Commit)
                                    clsApplicationSettings.ResetEditTime()
                                Catch ex As Exception
                                    DataTable.Close(clsDataTable.enumEndTransaction.Rollback)
                                Finally
                                    DataTable.Close()
                                End Try
                                Project.UpdateTrackingRecord(TableName, Project.GetRecordCount(TableName), True)
                                Project.UpdateOwnerField(TableName)
                                frmJournal.AddJournalEntry(TableName, frmJournal.enumJournalEntry.Calculate, NumComputed, , "Calculate TSI")
                                RefreshAllForms()
                                Project.DataSet.Tables.Remove("CrossTab")
                                Close()
                            End If
                            MainForm.OpenDataTable(TableName)
                        End With
                    Catch ex As Exception
                    Finally
                        DataTable.Close()
                    End Try
                Else
                    .SelectedIndex += 1
                End If
            End With
        Catch ex As Exception
            ErrorMsg(, ex)
        Finally
            frmJournal.ClearJournalDescription()
            MainForm.ProgressComplete()
        End Try
    End Sub

    Private Sub cboTableName_SelectionChangeCommitted(ByVal sender As Object, ByVal e As System.EventArgs) Handles cboTableName.SelectionChangeCommitted
        frmCalc.TableSelectionCommitted(cboTableName)
    End Sub

    Private Sub TabControl1_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles TabControl1.SelectedIndexChanged
        Try
            Select Case TabControl1.SelectedIndex
                Case 1
                    CalcType = enumCalcType.Invalid

                    Select Case cboMethod.SelectedIndex
                        Case 0 'carlson; allow only one parameter to be selected
                            If cboChl.SelectedIndex <> 0 And cboSecchi.SelectedIndex = 0 And cboTP.SelectedIndex = 0 Then CalcType = enumCalcType.Chl
                            If cboChl.SelectedIndex = 0 And cboSecchi.SelectedIndex <> 0 And cboTP.SelectedIndex = 0 Then CalcType = enumCalcType.Secchi
                            If cboChl.SelectedIndex = 0 And cboSecchi.SelectedIndex = 0 And cboTP.SelectedIndex <> 0 Then CalcType = enumCalcType.TP
                            If CalcType = enumCalcType.Invalid Then
                                WarningMsg("You selected an invalid combination of input parameters. You must select any one of Chlorophyll, Secchi Depth, or Total Phosphorus.")
                                Exit Sub
                            End If
                        Case 1 'Sigua; allow combinations
                            If cboChl.SelectedIndex <> 0 And cboSecchi.SelectedIndex = 0 And cboTP.SelectedIndex = 0 And cboTN.SelectedIndex = 0 Then CalcType = enumCalcType.Chl
                            If cboChl.SelectedIndex = 0 And cboSecchi.SelectedIndex <> 0 And cboTP.SelectedIndex = 0 And cboTN.SelectedIndex = 0 Then CalcType = enumCalcType.Secchi
                            If cboChl.SelectedIndex = 0 And cboSecchi.SelectedIndex = 0 And cboTP.SelectedIndex <> 0 And cboTN.SelectedIndex = 0 Then CalcType = enumCalcType.TP
                            If cboChl.SelectedIndex = 0 And cboSecchi.SelectedIndex = 0 And cboTP.SelectedIndex = 0 And cboTN.SelectedIndex <> 0 Then CalcType = enumCalcType.TN
                            If cboChl.SelectedIndex <> 0 And cboSecchi.SelectedIndex <> 0 Then
                                If cboTP.SelectedIndex <> 0 Then
                                    If cboTN.SelectedIndex <> 0 Then CalcType = enumCalcType.Chl_Secchi_TP_TN Else CalcType = enumCalcType.Chl_Secchi_TP
                                ElseIf cboTN.SelectedIndex <> 0 Then
                                    CalcType = enumCalcType.Chl_Secchi_TN
                                End If
                            End If
                            If CalcType = enumCalcType.Invalid Then
                                WarningMsg("You selected an invalid combination of input parameters. You must select any one of Chlorophyll, Secchi Depth, Total Phosphorus, or Total Nitrogen, or you must select the combination of Chlorophyll, Secchi Depth, and either or both of Total Phosphorus and Total Nitrogen.")
                                Exit Sub
                            End If
                        Case 2 'florida
                            If cboChl.SelectedIndex <> 0 And cboTN.SelectedIndex <> 0 And cboTP.SelectedIndex <> 0 Then CalcType = enumCalcType.Florida
                            If CalcType = enumCalcType.Invalid Then
                                WarningMsg("You must select all of Chlorophyll, Total Phosphorus, and Total Nitrogen.")
                                Exit Sub
                            End If
                    End Select

                    Dim NotSelected As Boolean = False
                    For i As Integer = 1 To 2
                        With CType(Choose(i, cboTSI, cboCCode), ComboBox)
                            If .SelectedIndex = -1 Then NotSelected = True : Exit For
                        End With
                    Next
                    If NotSelected Then
                        TabControl1.SelectedIndex = 0
                        Application.DoEvents()
                        WarningMsg("You must specify both the PCode and CCode of the calculated TSI values before you can continue.")
                        Exit Sub
                    End If
            End Select

            If TabControl1.SelectedIndex = 1 Then btnNext.Text = "&Finish" Else btnNext.Text = "&Next"

            If TabControl1.SelectedIndex >= 1 Then 'create raw data crosstab
                'user must select either one of four parameters or chl, secchi and one or both of tp and tn
                'equations are based on paper by G. C. Sigua, M. J. Williams, S. W. Coleman and R. Starks found at http://jeq.scijournals.org/cgi/content/full/35/1/240
                Dim sql As String = ""
                Select Case CalcType
                    Case enumCalcType.Chl, enumCalcType.Secchi, enumCalcType.TP, enumCalcType.TN
                        sql = String.Format("SELECT Station_ID,Date_Time,Result,0.0 AS TSI FROM {0} Dta WHERE PCode='{1}' AND Selected_By='{2}' ORDER BY Station_ID,Date_Time", clsProject.TempData, Project.GetPickList(Choose(CalcType + 1, cboChl, cboSecchi, cboTP, cboTN)), clsApplicationSettings.UserName)
                    Case enumCalcType.Chl_Secchi_TP, enumCalcType.Chl_Secchi_TN
                        sql = String.Format("SELECT D1.Station_ID AS Station_ID,D1.Date_Time AS Date_Time,D1.Result as CHL,D2.Result as SECCHI,D3.Result AS TN_TP,0.0 as TSI FROM {0} D1, {0} D2, {0} D3 WHERE D1.Station_ID=D2.Station_ID AND D1.Date_Time=D2.Date_Time AND D1.Station_ID=D3.Station_ID AND D1.Date_Time=D3.Date_Time AND D1.PCode='{1}' and D2.PCode='{2}' AND D3.PCode='{3}' AND D1.Selected_By='{4}' ORDER BY D1.Station_ID,D1.Date_Time", clsProject.TempData, Project.GetPickList(cboChl), Project.GetPickList(cboSecchi), Project.GetPickList(IIf(CalcType = enumCalcType.Chl_Secchi_TP, cboTP, cboTN)), clsApplicationSettings.UserName)
                    Case enumCalcType.Chl_Secchi_TP_TN
                        sql = String.Format("SELECT D1.Station_ID AS Station_ID,D1.Date_Time AS Date_Time,D1.Result as CHL,D2.Result as SECCHI,D3.Result AS TP,D4.Result AS TN,0.0 as TSI FROM {0} D1, {0} D2, {0} D3, {0} D4 WHERE D1.Station_ID=D2.Station_ID AND D1.Date_Time=D2.Date_Time AND D1.Station_ID=D3.Station_ID AND D1.Date_Time=D3.Date_Time AND D1.Station_ID=D4.Station_ID AND D1.Date_Time=D4.Date_Time AND D1.PCode='{1}' and D2.PCode='{2}' and D3.PCode='{3}' and D4.PCode='{4}' AND D1.Selected_By='{5}' ORDER BY D1.Station_ID,D1.Date_Time", clsProject.TempData, Project.GetPickList(cboChl), Project.GetPickList(cboSecchi), Project.GetPickList(cboTP), Project.GetPickList(cboTN), clsApplicationSettings.UserName)
                    Case enumCalcType.Florida
                        sql = String.Format("SELECT D1.Station_ID AS Station_ID,D1.Date_Time AS Date_Time,D1.Result as CHL,D2.Result AS TP,D3.Result AS TN,0.0 as TSI FROM {0} D1, {0} D2, {0} D3 WHERE D1.Station_ID=D2.Station_ID AND D1.Date_Time=D2.Date_Time AND D1.Station_ID=D3.Station_ID AND D1.Date_Time=D3.Date_Time AND D1.PCode='{1}' and D2.PCode='{2}' and D3.PCode='{3}' AND D1.Selected_By='{4}' ORDER BY D1.Station_ID,D1.Date_Time", clsProject.TempData, Project.GetPickList(cboChl), Project.GetPickList(cboTP), Project.GetPickList(cboTN), clsApplicationSettings.UserName)
                End Select

                MainForm.WriteProgress("Compiling data...", ProgressBarStyle.Marquee, True)
                If Project.DB.AddTable(Project.DataSet, "CrossTab", sql) Is Nothing Then
                    WarningMsg("Unable to create crosstab table. SQL string was: " & sql)
                    Exit Sub
                End If

                If Project.DataSet.Tables("CrossTab").Rows.Count = 0 Then
                    WarningMsg("No coincident values of the specified parameters were found.")
                    Exit Sub
                End If

                Dim i As Integer = 0
                For Each r As DataRow In Project.DataSet.Tables("CrossTab").Rows
                    Select Case CalcType
                        Case enumCalcType.Chl : r.Item(3) = Math.Round(TSI_Chl(r.Item(2)), 1)
                        Case enumCalcType.Secchi : r.Item(3) = Math.Round(TSI_Secchi(r.Item(2)), 1)
                        Case enumCalcType.TP : r.Item(3) = Math.Round(TSI_TP(r.Item(2)), 1)
                        Case enumCalcType.TN : r.Item(3) = Math.Round(TSI_TN(r.Item(2)), 1)
                        Case enumCalcType.Chl_Secchi_TP : r.Item(5) = Math.Round((TSI_Chl(r.Item(2)) + TSI_Secchi(r.Item(3)) + TSI_TP(r.Item(4))) / 3, 1)
                        Case enumCalcType.Chl_Secchi_TN : r.Item(5) = Math.Round((TSI_Chl(r.Item(2)) + TSI_Secchi(r.Item(3)) + TSI_TN(r.Item(4))) / 3, 1)
                        Case enumCalcType.Chl_Secchi_TP_TN : r.Item(6) = Math.Round((TSI_Chl(r.Item(2)) + TSI_Secchi(r.Item(3)) + (TSI_TP_B(r.Item(4)) + TSI_TN_B(r.Item(5)) / 2)) / 3, 1)
                        Case enumCalcType.Florida : r.Item(5) = Math.Round((TSI_Chl(r.Item(2)) + TSI_Nutr(r.Item(3), r.Item(4))) / 2, 1)
                    End Select
                    MainForm.WriteProgress(i, Project.DataSet.Tables("CrossTab").Rows.Count - 1, "Compiling data...", ProgressBarStyle.Marquee, True)
                    i += 1
                Next
                With dgRecords
                    .DataSource = Nothing
                    .Columns.Clear()
                    .DataSource = Project.DataSet.Tables("CrossTab")
                    .ReadOnly = True
                    .RowHeadersVisible = False
                    .AllowUserToAddRows = False
                    .AllowUserToDeleteRows = False
                    .AllowUserToOrderColumns = False
                    .AllowUserToResizeRows = False
                    .ColumnHeadersDefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter
                    For c As Integer = 0 To .ColumnCount - 1
                        With .Columns(c)
                            .SortMode = DataGridViewColumnSortMode.NotSortable
                            .HeaderText = Choose(c + 1, "Station ID", "Date-Time", "Chl", "Secchi", "TP", "TN", "TSI")
                            .DefaultCellStyle.Format = Choose(c + 1, "", MyDateTimeFormat, "N3", "N3", "N3", "N3", "N1")
                            .DefaultCellStyle.Alignment = Choose(c + 1, DataGridViewContentAlignment.MiddleLeft, DataGridViewContentAlignment.MiddleCenter, DataGridViewContentAlignment.MiddleRight, DataGridViewContentAlignment.MiddleRight, DataGridViewContentAlignment.MiddleRight, DataGridViewContentAlignment.MiddleRight, DataGridViewContentAlignment.MiddleRight)
                            .AutoSizeMode = Choose(c + 1, DataGridViewAutoSizeColumnMode.AllCells, DataGridViewAutoSizeColumnMode.AllCells, DataGridViewAutoSizeColumnMode.Fill, DataGridViewAutoSizeColumnMode.Fill, DataGridViewAutoSizeColumnMode.Fill, DataGridViewAutoSizeColumnMode.Fill, DataGridViewAutoSizeColumnMode.Fill)
                        End With
                    Next
                    Select Case CalcType
                        Case enumCalcType.Chl To enumCalcType.TN : .Columns(2).HeaderText = Choose(CalcType + 1, "Chl", "Secchi", "TP", "TN") : .Columns(3).HeaderText = "TSI" : .Columns(3).DefaultCellStyle.Format = "N1"
                        Case enumCalcType.Chl_Secchi_TP To enumCalcType.Chl_Secchi_TN : .Columns(4).HeaderText = IIf(CalcType = enumCalcType.Chl_Secchi_TP, "TP", "TN") : .Columns(5).HeaderText = "TSI" : .Columns(5).DefaultCellStyle.Format = "N1"
                        Case enumCalcType.Florida : .Columns(3).HeaderText = "TP" : .Columns(4).HeaderText = "TN" : .Columns(5).HeaderText = "TSI" : .Columns(5).DefaultCellStyle.Format = "N1"
                        Case enumCalcType.Chl_Secchi_TP_TN 'already good to go from above
                    End Select
                End With
            End If

        Catch ex As Exception
            ErrorMsg(, ex)
        Finally
            MainForm.ProgressComplete()
        End Try
    End Sub

    Private Function TSI_Chl(ByVal Chl As Double) As Double
        If Chl = 0 Then
            Return 0
        Else
            Select Case cboMethod.SelectedIndex
                Case 0 : Return 30.6 + 9.81 * Math.Log(Chl)
                Case 1, 2 : Return 16.8 + 14.4 * Math.Log(Chl)
            End Select
        End If
    End Function

    Private Function TSI_Secchi(ByVal Secchi As Double) As Double
        If Secchi = 0 Then
            Return 0
        Else
            Select Case cboMethod.SelectedIndex
                Case 0 : Return 60 - 14.41 * Math.Log(Secchi)
                Case 1 : Return 60 - 30 * Math.Log(Secchi)
                Case 2 : Return Double.MinValue
            End Select
        End If
    End Function

    Private Function TSI_TP(ByVal TP As Double) As Double
        'for phosphorus-limited lakes
        If TP = 0 Then
            Return 0
        Else
            Select Case cboMethod.SelectedIndex
                Case 0 : Return 4.15 + 14.42 * Math.Log(TP * 1000.0) 'convert mg/L to ug/L
                Case 1 : Return -23.8 + 23.6 * Math.Log(TP * 1000.0) 'convert mg/L to ug/L
                Case 2 : Return -18.4 + 18.6 * Math.Log(TP * 1000.0)
            End Select
        End If
    End Function

    Private Function TSI_TP2(ByVal TP As Double) As Double
        'for phosphorus-limited lakes
        If TP = 0 Then
            Return 0
        Else
            Select Case cboMethod.SelectedIndex
                Case 0, 1 : Return Double.MinValue
                Case 2 : Return 10 * (-2.38 + 2.36 * Math.Log(TP * 1000))
            End Select
        End If
    End Function

    Private Function TSI_TN(ByVal TN As Double) As Double
        'for nitrogen-limited lakes
        If TN = 0 Then
            Return 0
        Else
            Select Case cboMethod.SelectedIndex
                Case 0 : Return Double.MinValue
                Case 1 : Return 59.6 + 21.5 * Math.Log(TN * 1000.0) 'convert to ug/l
                Case 2 : Return 56 + 19.8 * Math.Log(TN)
            End Select
        End If
    End Function

    Private Function TSI_TN2(ByVal TN As Double) As Double
        'for nitrogen-limited lakes
        If TN = 0 Then
            Return 0
        Else
            Select Case cboMethod.SelectedIndex
                Case 0, 1 : Return Double.MinValue
                Case 2 : Return 10 * (5.96 + 2.15 * Math.Log(TN + 0.0001))
            End Select
        End If
    End Function

    Private Function TSI_TP_B(ByVal TP As Double) As Double
        'for nutrient-balanced lakes
        'convert mg/L to ug/L
        Return -18.4 + 18.6 * Math.Log(TP * 1000.0)
    End Function

    Private Function TSI_TN_B(ByVal TN As Double) As Double
        'for nutrient-balanced lakes
        'convert mg/L to ug/L
        Return 56 + 19.8 * Math.Log(TN * 1000.0)
    End Function

    Private Function TSI_Nutr(ByVal TP As Double, ByVal TN As Double) As Double
        If TN / TP > 30 Then
            Return TSI_TP2(TP)
        ElseIf TN / TP < 10 Then
            Return TSI_TN2(TN)
        Else
            Return (TSI_TP(TP) + TSI_TN(TN)) / 2
        End If
    End Function

    Private Sub btnHelp_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnHelp.Click
        ShowHelp(Me, "6")
    End Sub

    Private Sub cboMethod_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles cboMethod.SelectedIndexChanged
        lblTN.Enabled = False
        cboTN.Enabled = False
        lblSecchi.Enabled = False
        cboSecchi.Enabled = False
        Select Case cboMethod.SelectedIndex
            Case 0 'carlson
                lblSecchi.Enabled = True
                cboSecchi.Enabled = True
            Case 1 'sigua
                lblSecchi.Enabled = True
                cboSecchi.Enabled = True
                lblTN.Enabled = True
                cboTN.Enabled = True
            Case 2 'florida
                lblTN.Enabled = True
                cboTN.Enabled = True
        End Select
    End Sub

    Private Sub cboTSI_SelectionChangeCommitted(ByVal sender As Object, ByVal e As System.EventArgs) Handles cboTSI.SelectionChangeCommitted
        frmCalc.PCodeSelectionCommitted(cboTSI)
    End Sub

    Private Sub cboCCode_SelectionChangeCommitted(ByVal sender As Object, ByVal e As System.EventArgs) Handles cboCCode.SelectionChangeCommitted
        frmCalc.CCodeSelectionCommitted(cboCCode)
    End Sub

    Private Sub btnJournal_Click(sender As System.Object, e As System.EventArgs) Handles btnJournal.Click
        frmJournal.GetJournalDescription()
    End Sub
End Class
